﻿// copyright @ 2009 Farida
// Module : Employee Management , To Add ,Modify,Delete Records

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace ProjectMain
{

    class ClassAdminEmpMgmt
    {

        MySqlConnection conn = ConnectToDbClass.ConnectDb();

              public static void AdminEmpMgmt(string username)
        {
            int choice;

            ClassAdminEmpMgmt empmgmt = new ClassAdminEmpMgmt();
            choice = SubMenuEmpClass.SubMenuEmp();

            Console.WriteLine();
            Console.WriteLine("Employee Management Before ");
            Console.WriteLine("__________________________");
            // Execute Reader Method
            //empmgmt.ReadData();





            switch (choice)
            {
                case 1: Console.WriteLine();
                    Console.WriteLine("Employee Management Before ");
                    Console.WriteLine("__________________________");
                    // Execute Reader Method
                    empmgmt.ReadData();
                    empmgmt.InsertData();
                    // Execute Reader Method
                    Console.WriteLine("Employee Management After insert");
                    empmgmt.ReadData();
                    break;

                case 2: empmgmt.DeleteData();
                    // Execute Reader Method
                    Console.WriteLine("Employee Management After Delete");
                    empmgmt.ReadData();
                    break;

                case 3: empmgmt.UpdateData();
                    // Execute Reader Method
                    Console.WriteLine("Employee Management After Update");
                    empmgmt.ReadData();
                    break;

                case 4: Console.WriteLine("Employee Management System");
                    empmgmt.ReadData();
                    break;

                case 5: Console.WriteLine("Employee  Query Management System");
                    ClassStoredProcQuery.StoredProcQuery();

                    break;

			     case 6: Console.WriteLine("Employee  Query Management System");
                         SubMenu.SubMenuOptions("username");
				         break;
				



                default:
                    Console.WriteLine("{0} is not a valid choice", choice);
                    break;

            }


        }
        // ExecuteReader Method
        public void ReadData()
        {

            MySqlDataReader rdr = null;
            bool success;
            try
            {
                //open the connection

                conn.Open();

                //1. Instantiate the command with a query and connection

                MySqlCommand cmd = new MySqlCommand("select * from emp_mgmt", conn);

                //2. Call Execute Reader to get Query Results 

                rdr = cmd.ExecuteReader();

                // print each record 

                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0]);
                    // Console.WriteLine(rdr[1]);
                    // Console.WriteLine(rdr[2]);
                }
            }
            finally
            {
                //close the reader 
                if (rdr != null)
                {
                    rdr.Close();
                }
                //close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }

            
        }

        // use ExecuteNonQuery Method for Insert

        public void InsertData()
        {
            try
            {
                //Open the Connection
                conn.Open();

                //prepare Command String


                System.Console.WriteLine("Add Emp Id , date of Ownership, Product Id of new Employee");
                System.Console.WriteLine("Enter Employee Id");

                int uemp_id = Convert.ToInt16(Console.ReadLine());

                System.Console.WriteLine("Enter Date of ownerShip");

                string udate = Console.ReadLine();

                System.Console.WriteLine("Enter Product Id");

                string uprod_id = Console.ReadLine();



                System.Console.WriteLine("You entered following values:");
                System.Console.WriteLine("EmpId :" + uemp_id + " Date:" + udate + " Prod Id:" + uprod_id);

                string insertString = string.Format("insert into emp_mgmt(emp_id,own_date,prod_id) values  ({0},{1},{2} )", uemp_id, udate, uprod_id);

                //1.Instantiate the command witha query and connection

                MySqlCommand cmd = new MySqlCommand(insertString, conn);

                //2.Call ExecuteNonQuery 
                cmd.ExecuteNonQuery();

               
            }

            finally
            {

                //Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public void DeleteData()
        {
            try
            {
                //Open the Connection
                conn.Open();

                //prepare Command String
                System.Console.WriteLine("Enter Employee Id to Delete:");

                int @emp_id = Convert.ToInt16(Console.ReadLine());

                string DeleteString = string.Format("delete from emp_mgmt where emp_id = {0}", @emp_id);

                //string DeleteString = @"delete from emp_mgmt where  emp_id =@emp_id";

                Console.WriteLine(DeleteString);

                //1.Instantiate the command witha query and connection
                MySqlCommand cmd = new MySqlCommand(DeleteString, conn);

                //2.Call ExecuteNonQuery 
                cmd.ExecuteNonQuery();

            }

            finally
            {

                //Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }


        public void UpdateData()
        {
            try
            {
                //Open the Connection
                conn.Open();

                System.Console.WriteLine("Enter Employee Id needs to be Updated");

                int @emp_id = Convert.ToInt16(Console.ReadLine());

                System.Console.WriteLine("Enter New Employee Id needs to be Updated");

                int uemp_id = Convert.ToInt16(Console.ReadLine());



                //prepare Command String

                string UpdateString = string.Format("update emp_mgmt set emp_id ={0} where emp_id = {1}", uemp_id, @emp_id);


                //1.Instantiate the command witha query and connection
                MySqlCommand cmd = new MySqlCommand(UpdateString, conn);

                //2.Call ExecuteNonQuery 
                cmd.ExecuteNonQuery();

               
            }

            finally
            {

                //Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }



    }

}


















